<?php
// SQL 创建数据表 zodiac
/*CREATE TABLE zodiac (
    id INT UNSIGNED NOT NULL,
    sign CHAR(11),
    symbol CHAR(13),
    planet CHAR(7),
    element CHAR(5),
    start_month TINYINT,
    start_day TINYINT,
    end_month TINYINT,
    end_day TINYINT,
    PRIMARY KEY(id)
);

INSERT INTO zodiac VALUES (1, 'Aries', 'Ram', 'Mars', 'fire', 3, 21, 4, 19);
INSERT INTO zodiac VALUES (2, 'Taurus', 'Bull', 'Venus', 'earth', 4, 20, 5, 20);
INSERT INTO zodiac VALUES (3, 'Gemini', 'Twins', 'Mercury', 'air', 5, 21, 6, 21);
INSERT INTO zodiac VALUES (4, 'Cancer', 'Crab', 'Moon', 'water', 6, 22, 7 ,22);
INSERT INTO zodiac VALUES (5, 'Leo', 'Lion', 'Sun', 'fire', 7, 23, 8, 22);
INSERT INTO zodiac VALUES (6, 'Virgo', 'Virgin', 'Mercury', 'fire', 8, 23, 9, 22);
INSERT INTO zodiac VALUES (7, 'Libra', 'Scales', 'Venus', 'air', 9, 23, 10, 23);
INSERT INTO zodiac VALUES (8, 'Scorpio', 'Scorpion', 'Mars', 'water', 10, 24, 11, 21);
INSERT INTO zodiac VALUES (9, 'Sagittarius', 'Archer', 'Jupiter', 'fire', 11, 22, 12, 121);
INSERT INTO zodiac VALUES (10, 'Capricorn', 'Goat', 'Saturn', 'earth', 12, 22, 1, 19);
INSERT INTO zodiac VALUES (11, 'Aquarius', 'Water Carrier', 'Uranus', 'air', 1, 20, 2, 18);
INSERT INTO zodiac VALUES (12, 'Pisces', 'Fishes', 'Neptune', 'water', 2, 19, 3, 20);*/

//10.1 使用 DBM 数据库
/*$dbh = dba_open(__DIR__ . '/fish.db', 'c', 'db4') or die($php_errormsg);

//获取和修改值
if(dba_exists('flounder', $dbh)){
    $flounder_count = dba_fetch('flounder', $dbh);
    $flounder_count++;
    dba_replace('flounder', $flounder_count, $dbh);
    print "Updated the flounder count.";
}else{
    dba_insert('flounder', 1, $dbh);
    print "Started the flounder count.";
}

//没有罗非鱼
dba_delete('tilapia', 1, $dbh);

//我们有什么鱼？
for ($key = dba_firstkey($dbh); $key !== false; $key = dba_nextkey($dbh)){
    $value = dba_fetch($key, $dbh);
    print "$key: $value\n";
}
dba_close($dbh);*/


//10.2 使用 SQLite 数据库
$db = new PDO('sqlite:zodiac.db');
$sth = $db->prepare('select * from zodiac');
$sth->execute();
if (count($sth->fetchAll())) {
    print "This form has already been submitted!<br />";
} else {
    $sth = $db->prepare('INSERT INTO zodiac VALUES (1, \'Aries\', \'Ram\', \'Mars\', \'fire\', 3, 21, 4, 19)');
    $sth->execute();
    print "The form is submitted successfully.<br />";
}

/*//10.3 链接 SQL 数据库    使用 PDO 链接
$user = 'root';
$password = '';

// 希望连接字符串中提供参数
$mysql = new PDO('mysql:host=localhost', $user, $password);

// 多个参数之间使用；分割
$mysql = new PDO('mysql:host=localhost;port=3306', $user, $password);
$mysql = new PDO('mysql:host=localhost;port=3306;dbname=test', $user, $password);

//连接本地 MYSQL 数据库
$mysql = new PDO('mysql:UNIX_socket=/tmp/mysql.sock', $user, $password);


// PostgreSQL 也希望连接字符串中提供参数
$pgsql = new PDO('pgsql:host=db.example.com', $user, $password);

// 多个参数之间用 ' ' 或 ; 分隔
$pgsql = new PDO('pgsql:host=db.example.com port=31075', $user, $password);
$pgsql = new PDO('pgsql:host=db.example.com;port=31075;dbname=food', $user, $password);

// 如果愿意  可以将用户名和密码放在 DSN 中
$pgsql = new PDO("pgsql:host=db.example.com port=31075 dbname=food user=$user password=$password");

// Orancle   如果数据库名在 tnsnames.ora 中定义， 将它放在 DSN 中，作为 dbname 的值
$oci = new PDO('oci:dbname=food', $user, $password);
//否则，指定一个 Instant Client URI
$oci = new PDO('oci:dbname=//db.example.com:1521/food', $user, $password);

// Sybase (如果 PDO 使用 Sybase 的 ct-lib 库)
$sybase = new PDO('sybase:host=db.example.com;dbname=food', $user, $password);
// Microsoft SQL Server (如果 PDO 使用 MS SQL Server 库)
$mssql = new PDO('mssql:host=db.example.com;dbname=food', $user, $password);
//DBLib (面向 FreeTDF)
$dblib = new PDO('dblib:host=db.example.com;dbname=food', $user, $password);

// ODBC , 预定义连接
$odbc = new PDO('odbc:food');
// ODBC , ad-hoc 连接。 提供底层驱动程序需要的信息
$odbc = new Pdo('odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\data\\food.mdb;Uid=Chef');

//SQLite 只需要一个文件名， 不需要用户名和密码
$sqlite = new PDO('sqlite:/usr/local/zodiac.db');
$sqlite = new PDO('sqlite:c:/data/zodiac.db');
//SQLite 还可以处理内存中的临时数据库
$sqlite = new PDO('sqlite::memory:');
//SQLite v2 DSN 类似 v3
$sqlite2 = new PDO('sqlite2:/usr/local/old-zodiac.db');*/


//10.4 查询 SQL 数据库
// 10-9 向数据库发送查询
$st = $db->query('select symbol, planet from zodiac');
foreach ($st->fetchAll() as $row) {
    print "{$row['symbol']} goes with {$row['planet']} <br/> \n";
}

// 10-10 获取各行
$rows = $db->query('select symbol, planet from zodiac order by planet');
$firstRow = $rows->fetch();
print "The first results are that {$firstRow['symbol']} goes with {$firstRow['planet']} <br/>\n";

// 10-11 绑定结果列
$row = $db->query('select symbol, planet from zodiac', PDO::FETCH_BOUND);
$row->bindColumn('symbol', $symbol);
$row->bindColumn(2, $planet);
while ($row->fetch()){
    print "$symbol goes with $planet. <br/>\n";
}

//10-12 扩展 PDOStatement
class AvgStatement extends PDOStatement{
    public function avg()
    {
        $sum = 0;
        $vars = get_object_vars($this);
        //删除 DPOStatement 的内置 'queryString' 变量
        unset($vars['queryString']);
        foreach ($vars as $var => $value){
            $sum += strlen($value);
        }
        return $sum / count($vars);
    }
}
$row = new AvgStatement();
$results = $db->query('select symbol, planet from zodiac', PDO::FETCH_INTO, $row);

//每次调用 fetch() 时， 都会重新填充 $row
while ($results->fetch()){
    print "$row->symbol belongs to $row->planet (Average: {$row->avg()}) <br />\n";
}

//10.5 不用循环获取记录行
//10-13 以此获取所有结果
$st = $db->query('select planet, element from zodiac');
$results = $st->fetchAll();
foreach ($results as $i => $result){
    print "Planet $i is {$result['planet']} <br/>\n";
}

//10.6 修改 SQL 数据库中的数据
$db->exec('INSERT INTO zodiac(id, sign) VALUES (13, "slagga")');
$db->exec('update zodiac set sign = "tom" where id = 13');
$db->exec('delete from zodiac where sign like "tom"');

$st = $db->prepare('insert into zodiac(id, sign) values (?, ?)');
$st->execute([13, 'Vito']);

$st = $db->prepare('update zodiac set sign=? where id=?');
$st->execute(['Fredo', 13]);

$st = $db->prepare('delete from zodiac where sign like ?');
$st->execute(['Fredo']);

//10.7 高效地重复执行查询
//10-17 准备
$st = $db->prepare('select sign from zodiac where element like ?');
//执行一次
$st->execute(['fire']);
while ($row = $st->fetch()){
    print "$row[0].<br>\n";
}
//再次执行
$st->execute(['water']);
while ($row = $st->fetch()){
    print "$row[0]. <br>\n";
}

//10-18 多个占位符
$st = $db->prepare('select sign from zodiac where element like ? or planet like ?');
$st->execute(['earth', 'Mars']);
$row = $st->fetch();
print_r($row);

//10-19 使用命名占位符
$st = $db->prepare('select sign from zodiac where element like :element or planet like :planet');
$st->execute(['planet' => 'Mars', 'element' => 'earth']);
$row = $st->fetch();
print_r($row);

//10-20 使用 bndParam()
$pairs = ['Mars' => 'water', 'Moon' => 'water', 'Sun' => 'fire'];
$st = $db->prepare('select sign from zodiac where element like :element and planet like :planet');
$st->bindParam(':element', $element);
$st->bindParam(':planet', $planet);
foreach ($pairs as $planet => $element){
    // 不需要向 execute() 传入任何值  值来自 $element 和 $planet
    $st->execute();
    var_dump($st->fetch());
}

//10-21 用 PDO::PARAM_LOB 将文件内容放入数据库     需要新建 files 数据表
/*DROP TABLE IF EXISTS "main"."files";
CREATE TABLE "files" (
"path"  TEXT,
"contents"  TEXT
);*/
$st = $db->prepare('insert into files (path, contents) values (:path, :contents)');
$st->bindParam(':path', $path);
$st->bindParam(':contents', $fp, PDO::PARAM_LOB);
foreach (glob('C:\xampp\apache\logs\*') as $path) {
    $fp = fopen($path, 'r');
    $st->execute();
}

//10.8 查看查询返回的行数
//10-22 用 rowCount() 统计行数
$st = $db->prepare('delete from files where path like ?');
$st->execute(['%access.log']);
print "Deleted rows: ".$st->rowCount();
$st->execute(['%error.log']);
print "Deleted rows: ".$st->rowCount();
$st->execute(['%install.log']);
print "Deleted rows: ".$st->rowCount();

//10-23 统计 select 返回的行数
$st = $db->query('select symbol, planet from zodiac');
$all = $st->fetchAll(PDO::FETCH_COLUMN, 1);
print "Retrieved ".count($all)." rows";

//10.9 转义引号
//10-24 手动加引号
$safe = $db->quote('Mars');
$safe = strtr($safe, ['_' => '\_', '%' => '\%']);
$st = $db->query("select * from zodiac where planet like $safe");
print_r($st->fetch());

//10-25 检查魔法引号
// magic_quotes_sybase 的行为也会带来影响
if(get_magic_quotes_gpc() && (!ini_get('magic_quotes_sybase'))){
    $fruit = stripslashes('C:\xampp\apache\logs\httpd.pid');
}else{
    $fruit = 'C:\xampp\apache\logs\httpd.pid';
}
$st = $db->prepare("update files set contents = 'slagga' where path = ?");
$st->execute([$fruit]);

//10.10 记录调试信息和错误
//实例10-26 打印错误信息
$st = $db->prepare('select * from imaginary_table');
if(!$st){
    $error = $db->errorInfo();
    print "Problem ({$error[2]})<br>";
}

//实例10-27 捕获数据库异常
//try{
//    $db = new PDO('sqlite:zodiac.db');
//    //让所有DB错误抛出异常
//    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//    $st = $db->prepare('select * from zodiac');
//    $st->execute();
//    while ($row = $st->fetch(PDO::FETCH_NUM)){
//        print implode(',', $row) . "<br/>\n";
//    }
//}catch (Exception $e){
//    print "Database Problem: " . $e->getMessage();
//}

//实例 10.11 创建唯一标识符
//实例10-28 创建唯一标识符
$st = $db->prepare('insert into files(path, contents) values (?, ?)');
$st->execute([uniqid(), 'Jacob']);
$st->execute([md5(uniqid()), 'Ruby']);

//实例10-29 用 sqlite 创建一个自动增长的列
//类型声明INTEGER PRIMARY KEY AUTOINCREMENT是在告诉SQLite分配递增的ID值
$db->exec(<<<_SQL_
    CREATE TABLE users(
        id  INTEGER PRIMARY KEYAUTOINCREMENT,
        name VARCHAR(255)
    )
_SQL_
);

//不需要为“id”插入值一 SQLite会自动为它赋值
$st = $db->prepare('INSERT INT0 users(name) VALUES(?)');

//插入的行都会给“id”列赋值
foreach (array(']acob', 'Ruby') as $name) {
    $st->execute(array($name));
}

//例10-30: 在MySQL中创建一个自动递增的列
// AUT0_I NCREMENT告诉MySQL分配递增的 ID值//而该列必须是主键(PRIMARY KEY)
$db->exec(<<<_SQL_
    CREATE TABLE users(id  INT NOT NULL AUT0_INCREMENT,
        name VARCHAR(255),
        PRIMARY KEY(id)
    )
_SQL_
);

//不需要为"id" 插入值 ---- MySQL会自动为它赋值
$st = $db->prepare('INSERT INT0 users(name) VALllES(?)');

//  插入的行都会给“id”列赋值
foreach (array(']acob', 'Ruby') as $name) {
    $st->execute(array($name));
}

//例10-31 : 构建一个UPDATE查询
// 一个字段名列表
$fields = array('symbol', 'planet', 'element');

$update_fields = array();
$update_values = array();
foreach ($fields as $field) {
    $update_fields[] = "$field = ?";
    //假定数据来自表単
    $update_values[] = $_POST[$field];
}

$st = $db->prepare("UPDATE zodiac SET" . implode(',', $update_fields) . "WHERE sign = ?");

// 把 sign 的值添加到值数组中
$update_values[] = $_GET['sign'];

$st->execute($update_values);

//例10-32 : 建立一个 Insert 查询
$field = ['symbol', 'planet', 'element'];
$placeholders = [];
$values = [];
foreach ($fields as $field) {
    $palceholders[] = '?';
    $values[] = $_POST[$field];
}

$st = $db->prepare('Insert into zodiac (' . implode(',', $fields) . ') values (' . implode(',', $placeholders) . ')');
$st->execute();

//例10-33 : bulid_query()
function pc_build_query($db, $key_field, $fields, $table)
{
    $values = [];
    if (!empty($_POST[$key_field])) {
        $update_fields = [];
        foreach ($fields as $field) {
            $update_fields[] = "$field = ?";
            //假设数据来自一个表单
            $values[] = $_POST[$field];
        }
        //将键字段的值增加到 $values 数组
        $values[] = $_POST[$key_field];
        $st = $db->prepare("Update $table set " . implode(',', $update_fields) . "where $key_field = ?");
    } else {
        $values[] = md5(uniqid());
        $placeholders = ['?'];
        foreach ($fields as $field) {
            $placeholders[] = "?";
            $values[] = $_POST[$field];
        }
        $st = $db->prepare("insert into $table ($key_field," . implode(',', $fields) . ') values (' . implode(',', $placeholders) . ')');
    }
    $st->execute();
    return $st;
}

//例10-34: 一个简单的添加/编辑记录的页面
$db = new  PD0('sqlite:/usr/loca1/data/zodiac.db');
$db->setAttribute(PD0::ATTR_ERRMODE, PD0::ERRMODE_EXCEPTION);

$fields = array('sign', 'symbol', 'planet', 'element',
    'start_month', 'start_day', ' end_month', 'end_day');

$cmd = isset($_REQUEST['cmd']) ? $_REQUEST['cmd'] : 'show';

switch ($cmd) {
    case'edit':
        try {
            $st = $db->prepare('SELECT' . implode(',', $fields) . ' FROM zodiac WHERE id=?');
            $st->execute(array($_REQUEST['id']));
            $row = $st->fetch(PD0::FETCH_ASSOC);
        } catch (Exception$e) {
            $row = [];
        }
    case'add':
        print '<form method="post" action="' . htmlentities($_SERVER['PHP_SELF']) . '">';
        print '<input type="hidden" name="cmd" value="save">';
        print '<table>';
        if ('edit' == $_REQUEST['cmd']) {
            printf('<input type="hiddenu name=uid” value="%d·>',
                $_REQUEST[' id']);
        }
        foreach ($fields as $field) {
            if ('edit' == $_REQUEST['cmd']) {
                $value = htmlentities($row[$field]);
            } else {
                $value = '';
            }
            printf('<tr><td>%s: </td><td><,nput type="text· name=u%su value=n%s">', $field, $field, $value);
            printf('</td></tr>');
        }
        print'<tr><td></td><td><input type="submit· value="Save"></td></tr>';
        print '</table></form>';
        break;
    case 'save':
        try {
            $st = pc_build_query($db, 'id', $fields, 'zodiac');
            print 'Added info.';
        } catch (Exception$e) {
            print "Couldn't add info: " . htmlentities($e->getMessage());
        }
        print "<hr>";
    case 'show':
    default:
        $self = htmlentities($_SERVER['PHP_SELF']);
        print'<u1>';
        foreach ($db->query('SELECT id,sign FROM zodiac') as $row) {
            printf('<li> <a href=u%s?cmd:editaid=%sH>%s</a>', $self, $row[' id'], $row['sign']);
        }
        print '<hr><li> <a href="' . $self . '?cmd=add">Add New</a>';
        print '</ul>';
        break;
}

//例10-35 : 通过SQLite实现分页
//从第3行开始,选择5行
foreach ($db->query('SELECT* FROM zodiac' . '0RDER BY sign LIMIT5 ' . '0FFSET l;') as $row) {
    //对每一条记录进行处理
}

//例10-36:  显示分页结果
$offset = isset($_GET['offset']) ? intval($_GET['offset']) : 1;
if (!$offset) {
    $offset = 1;
}
$per_page = 5;
$tota1 = $db->query('SELECT COuNT(*) FROM zodiac')->fetchColumn(0);


$limitedSQL = 'SELECT* FROM zodiac ORDER BY id ' . "LIMIT $per_page OFFSET" . ($offset - 1);
$lastRowNumber = $offset - 1;

foreach ($db->query($limitedSQL) as $row) {
    $lastRowNumber++;
    print "{$row['sign']}, {$row['symbo1']} ({$row['id']}) <br/>\n";
}

pc_indexed_links($total, $offset, $per_page);
print "<br/)";
print "(Displaying $offset - $lastRowNumber of $total)";

//例10-37 :  pc_print_links
function pc_print_links($inactive, $text, $offset = '')
{
    if ($inactive) {
        print "<span class='inactive'>$text</span>";
    } else {
        print "<span class='active')" . "<a href='" . htmlentities($_SERVER['PHP_SELF']) . "?offset=$offset'>$text</a></span>";
    }
}

//例10-38 : pc_indexed_links
function pc_indexed_links($total, $offset, $per_page)
{
    $separator = ' | ';

    pc_print_links($offset == 1, '<<Prev', $offset - $per_page);

    // 输出除最后一个之外的金部分组
    for ($start = 1, $end = $per_page; $end < $total; $start += $per_page, $end += $per_page) {
        print $separator;
        pc_print_link($offset == $start, "$start - $end", $start);
    }

    $end = ($total > $start) ? '-$total' : '';
    print $separator;
    pc_print_link($offset == $start, "$start$end", $start);

    print $separator;
    pc_print_link($offset == $start, 'Next>>', $offset + $per_page);
}

//例10-39  缓存查询的结果
require_once 'Cache/Lite.php';

$opts = array(
    //把缓存的数据放在何处
    'cacheDir' => 'c/tmp',
    //我们在缓存中保存数组
    'automaticSerialization' => true,
    //缓存中的信息保存多长时间
    'lifeTime' => 600/* 10分钟*/
);

//创建缓存
$cache = new Cache_Lite($opts);

//連接到数据庫
$db = new PD0('sqlite:c/data/zodiac.db');

//定义査询及其参数
$sql = 'SELECT * FROM zodiac WHERE planet= ?';
$params = array($_GET['planet']);

//取得唯一的缓存键
$key = cache_key($sql, $params);

//尝试从缓存中取得结果
$results = $cache->get($key);

if ($results === false) {
//没找到结果,那么执行査询并将结果放入缓存中
    $st = $db->prepare($sql);
    $st->execute($params);
    $results = $st->fetchAl1();
    $cache->save($results);
}

//无论是否取自缓存, $results中都保存我们的数据
foreach ($results as $result) {
    print "$result[id] : $result[planet], $result[sign] <br/>\n";
}

function cache_key($sql, $params)
{
    return md5($sql . implode('|', array_keys($params)) . implode('|', $params));
}

//建一个数据库连接

class DBCxn
{                                                      //要连接到哪个DSN1l
    public static $dsn = 'sqlite:c/data/zodiac.db';
    public static $user = null;
    public static $pass = null;
    public static $driverOpts = null;


    //保存連接的内部变量
    private static $db;

    //不允许克隆或技巧化
    final private function __construct()
    {
    }

    final private function __c1one()
    {
    }


    public static function get()
    {
        //如果不存在連接,则进行连接
        if (is_null(self::$db)) {
            self::$db = new P00(self::$dsn, self::$user, self::$pass, self::$driverOpts);
        }
        //返回連接
        return self::$db;
    }
}

//例10-41:  处理对多个投据库的速接
class DBCnn
{
    //要連接到哪个DSN
    public static $dsn = ['zodiac' => ' sqlite:c/data/zodiac.db', 'users' => ['mysql:host=db.example.com', 'monty', '7f2iuh'],
        'stats' => ['oci:statistics', 'stat5user', 'statspass']];

    //保存連接的内部变量
    private static $db = [];

    //不允许克隆或技巧化
    final private function __construct()
    {
    }

    final private function __clone()
    {
    }


    public static function get($key)
    {
        if (!isset(self::$dsn[$key])) {
            throw new Exception("Unknown DSN: $key");
        }

        //如果不存在連控,则进行連接
        if (!isset(self::$db[$key])) {
            if (is_array(self::$dsn[$key])) {
                $c = new ReflectionClass('PDO');
                self::$db[$key] = $c->newInstanceArgs(self::$dsn[$key]);
            } else {
                self::$db[$key] = new PD0(self::$dsn[$key]);
            }
        }
        //返回連接
        return self::$db[$key];
    }
}

//例10-42 : 在PHP的旧版本中调用 PD0构造器
$args = self::$dsn[$key];
$argCount = count($args);
if ($argCount == 1) {
    self::$db[$key] = new PD0($args[0]);
} else if ($argCount == 2) {
    self::$db[$key] = new PD0(Sargs[o], $args[1]);
} else if ($argCount == 3) {
    self::$db[$key] = new PD0($args[0], $args[1], $args[2]);
} else if ($argCount == 4) {
    self::$db[$key] = new PD0($args[0], $args[1], $args[2], $args[3]);
}

//用于保存留言的表结构如下:
//
//CREATE TABLE pc_message(
//    idINT uNSICINED NOT NuLL,
//    posted_on DATETIME NOT NuLL,
//    author CHAR(255),
//    sub:1ect CHAR(255),
//    body MEDIUMTEXT,
//    thread_id INT UNSICNED NOT 1llULL, parent_id INT UNSICNED NOT NULL, levelINT uNSIGNED NOT NULL,
//    thread_pos INT UNSICNED NOT NULL, PRIMARY KEY(id)
//);


//例10-43 :  message.php
$board = new MessageBoard();
$board->go();


class MessageBoard
{
    protected $db;
    protected $form_errors = array();
    protected $inTransaction = false;

    public function __construct()
    {
        set_exception_handler(array($this, ' logAndDie'));
        $this->db = new PD0('sc1lite:/usr/local/data/message.db');
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function go()
    {
        //$_REQUEST['cmd']值告诉我们要做什么
        $cmd = isset($_REQUEST['cmd']) ? $_REQUEST['cmd'] : 'show';
        switch ($cmd) {
            case 'read':                 //读取个别留言
                $this->read();
                break;
            case 'post':
                $this->post();
                break;
            case 'save':
                if ($this->valid()) {
                    $this->save();
                    $this->show();
                } else {
                    $this->post();
                }
                break;
            case 'show':
            default:
                $this->show();
                break;
        }
    }


    // save()把留言保存到数据库中
    protected function save()
    {
        $parent_id = isset($_REQUEST['parent_id']) ? intval($_REQUEST['parent_id']) : 0;
        //确保我们在使用pc_message时,它不会改变
        $this->db->beginTransaction();
        $this->inTransaction = true;

        // 这个留言是回复吗?
        if ($parent_id) {
            //取得父留言的线索、级别和thread_pos值
            $st = $this->db->prepare("SELECT thread_id,level,thread_pos FROM pc_message WHERE id= ?");
            $st->execute(array($parent_id));
            $parent = $st->fetch();
            // 回复的级别是其父留言级别加1 $level=$parent[·level']+1,
            /* 该线索中具有相同父留言的留言里面,,最大的thread_pos值是多少? */
            $st = $this->db->prepare('SELECT MAX(thread_pos) FROM pc_message WHERE thread_id=? AND parent_id=?');
            $st->execute(array($parent['thread_id'], $parent_id));
            $thread_pos = $st->fetchColumn(0);

            // 存在对这个父留言的回复吗?
            if ($thread_pos) {
                // thread_pos值加1
                $thread_pos++;
            } else {
                //这是第一条回复,所以排在父留言之后
                $thread_pos = $parent['thread_pos'] + 1;
            }

            /*给线索中在此条會言之后的所有富言的 thread_pos 都加上1*/
            $st = $this->db->prepare('UPDATE pc_message SET thread_pos = thread_pos+1 WHERE thread_id = ? AND thread_pos >= ?');
            $st->execute(array($parent['thread_id'], $thread_pos));

            //新留言中应法保存父留言的thread_id
            $thread_id = $parent['thread_id'];
        } else {
            //该留言不是回复,所以建立一条新线素
            $thread_id = $this->db->query('SELECT MAX(thread_id)+1 FROM pc_message')->fetchColumn(0);
            $level = 0;
            $thread_pos = 0;
        }


        /*把留言描入到数据庫中。使用prepare() 和execute() 保证所有字段都能被适当地引用转义 */
        $st = $this->db->prepare("INSERT INTO pc_message (id, thread_id, parent_id,thread_pos, posted_on, level, author,subject, body) VALllES(?,?,?,?,?,?,?,?,?)");
        $st->execute(array(null, $thread_id, $parent_id, $thread_pos, date('c'), $level, $_REQUEST['author'], $_REQUEST['subject'], $_REQUEST['body']));

        //提交所有操作
        $this->db->commit();
        $this->inTransaction = false;
    }

    // show() 用手显示所有留言的列表
    protected function show()
    {
        print '<h2)Message List</h2><p>';

        /*根据线索(thread_id)及各自在线素中的位置(thread_pos) 对留言进行排序*/
        $st = $this->db->query("SELECT id,author,subject,LENGTH(body) AS body_length, posted_on,level FROM pc_message OROER BY thread_id,thread_pos");
        while ($row = $st->Fetch()) {
            // 对于级别大子 0 的留言缩进显示
            print str_repeat('&nbsp;', 4 * $row['level']);
            //输出可以读取该留言信息的链接
            print "<a href='" . htmlentities($_SERVER['PHP_SELF']) . "?cmd=read&amp;id={$row['id']}'>" . htmlentities($row['subject']) . '</a> by ' . htmlentities($row['author']) . ' @ ' .
                htmlentities($row['posted_on']) . "{$row['body_length']} bytes) <br/>";
        }

        // 提供一种发表非回复留言的途径
        print  "<hr/><a href='" . htmlentities($_SERVER['PHP_SELF']) . "?cmd=post'>Start a New Thread</a>";
    }

    // read()用手显示个别的留言
    public function read()
    {
        /*确保我们传递的留言id是一个整数,并且 的确代表一条留言*/
        if (!isset($_REOUEST['id'])) {
            throw new Exception('No message ID supplied');
        }
        $id = intlva1($_REOUEST['id']);
        $st = $this->db->prepare("SELECT author,subject,body,posted_on FROM pc_message WHERE id= ?");
        $st->execute(array($id));
        $msg = $st->fetch();
        if (!$msg) {
            throw new Exception('Bad message ID');
        }

        /*不显示用户键入的HTML代码,但是显示在HTML換行时显示換行符*/
        $body = nl2br(htmlentities($msg['body']));

        // 显示.留言、 回复链接及返回留言列表的链接
        $self = htmlentities($_SERVER['PHP_SELF']);
        $subject = htmlentities($msg['subject']);
        $author = htmlentities($msg['author']);
        print <<<_HTML_
<h2>$subject</h2>
<h3>by$author</h3>
<p>$body</p>
<hr/>
<a href="$self?cmd=post&parent_id=$id">Reply</a>
<br/>
<a href="$self?cmd=1ist">List Messages</a >
_HTML_;
    }


    // post()显示发表留言的表单
    public function post()
    {
        $safe = array();
        foreach (array('author', 'subject', 'body') as $field) {
            //对默认字段值中的字符进行转义
            if (isset($_POST[$field])) {
                $safe[$field] = htmlentities($_POST[$field]);
            } else {
                $safe[$field] = '';
            }
            //用红色显示错误信息
            if (isset($this->form_ernrs[$field])) {
                $this->form_errors[$field] = '<span style="color: red">' . $this->form_errors[$field] . '</span><br />';
            } else {
                $this->form_errors[$field] = '';
            }
        }

        //该留言是回复么
        if (isset($_REQUEST['parent_id']) && $parend_id = intval($_REQUEST['parent_id'])) {
            //提交表单时发送 parent_id
            $parent_field = sprintf('<input type="hidden" name="parent_id" value="%d" />', $parend_id);

            if (!strlen($safe['subject'])) {
                $st = $this->db->prepare('select subject from message where id = ?');
                $st->execute([$parend_id]);

                $parent_subject = $st->fetchColumn(0);

                $safe['subject'] = htmlentities($parent_subject);
                if ($parent_subject && (!preg_match('/^re:/i', $parent_subject))) {
                    $safe['subject'] = "Re: {$safe['subject']}";
                }
            }
        } else {
            $parent_field = '';
        }
        //显示提交表单 并提供错误和默认值
        $self = htmlentities($_SERVER['PHP_SELF']);
        print <<<_HTML_
<form method="post" action="$self">
<table>
<tr>
 <td>Your Name:</td>
 <td>{$this->form_errors['author']}
     <input type="text" name="author" value="{$safe['author']}" />
 </td>
<tr>
 <td>Subject:</td>
 <td>{$this->form_errors['subject']}
     <input type="text" name="subject" value="{$safe['subject']}"
 </td>
<tr>
 <td>Message:</td>
 <td>{$this->form_errors['body']}
     <textarea rows="4" cols="30" wrap="physical" name="body">{$safe['body']}</textarea>
 </td>
<tr><td colspan="2"><input type="submit" value="Post Message" /></td></tr>
</table>
$parent_field
<input type="hidden" name="cmd" value="save" />
</form>
_HTML_;

    }

    public function valid()
    {
        $this->form_errors = [];
        if (!(isset($_POST['author'])) && strlen(trim($_POST['author']))) {
            $this->form_errors['author'] = 'Please enter your name.';
        }
        if (!(isset($_POST['subject'])) && strlen(trim($_POST['subject']))) {
            $this->form_errors['subject'] = 'Please enter a message subject.';
        }
        if (!(isset($_POST['body'])) && strlen(trim($_POST['body']))) {
            $this->form_errors['body'] = 'Please enter a message body.';
        }

        return (count($this->form_errors) == 0);
    }

    public function logAndDie(Exception $e)
    {
        print 'ERROR: ' . htmlentities($e->getMessage());
        if ($this->db && $this->db->inTransaction()) {
            $this->db->rollback();
        }
        exit();
    }
}

//10.17 Use Redis By Extend
$redis = new Redis();
$redis->connect('127.0.0.1');
$redis->set('counter', 0);
$redis->incrBy('counter', 7);
$counter = $redis->get('counter');
print $counter;

//Use Redis By Predis Library
require 'Predis/Autoloader.php';
Predis\Autoloader::register();

$redis = new Predis\Client(['host' => '127.0.0.1']);
$redis->set('counter', 0);
$redis->incrBy('counter', 7);
$counter = $redis->get('counter');
print $counter;